import pymysql
from time import sleep
import re

class humanClean:
    host='192.168.0.113'
    port=3306
    user='coname'
    passwd='xZS6p4LDcKCwn4Yb'
    charset='utf8'
    db='co'

    def open_mysql(self):#连接数据库
        try:
            self.conn = pymysql.connect(host=self.host,port=self.port,user=self.user,passwd=self.passwd,db=self.db,charset=self.charset)
            self.cursor=self.conn.cursor()
            print("连接数据库成功!")
            return self.conn,self.cursor
        except Exception as e:
            print("连接数据库失败>>>",e)

    def output_mysql(self):#操作数据库
        conn,cursor = self.open_mysql()#获取数据库连接
        cursor.execute("select content,srname from co_human_source")
        datas = cursor.fetchall()
        self.close_mysql(conn,cursor)
        return datas

    def input_mysql(self,data):
        conn,cursor = self.open_mysql()#获取数据库连接
        content=data[0]
        srname=data[1]
        #获取下面的字段
        srboss = re.findall('(法定代表人|法人代表)[\r|\n|\s]+(.*?)[\n|\r|\s]+',content)
        if len(srboss)>0:
            srboss=srboss[0][1]
        else:
            srboss=""
        
        srregistcard=re.findall('注册号[\n|\r|\s]+(.*?)[\r|\n|\s]+',content)
        if len(srregistcard)>0:
            srregistcard=srregistcard[0]
        else:
            srregistcard=""

        srloneycard=re.findall('统一社会信用代码[\n\r\s]+(.*?)[\r\n\s]+',content)
        if len(srloneycard)>0:
            srloneycard=srloneycard[0]
        else:
            srloneycard=""

        srbase = re.findall('注册资本[\n\r\s\d]+\w+[\r\n\s]',content)
        try:
            if len(srbase)==1 and srbase[0]!='-':
                try:
                    srbase=srbase[0]
                    srbase=re.findall('\d+\w+币$',srbase)[0]
                except Exception as e:
                    print("问题出在>>>",srname,e)
            elif len(srbase)==2:
                srbase=srbase[1]
                srbase=re.findall('\d+\w+币$',srbase)[0]
                if len(srbase)>0:
                    try:
                        srbase = srbase
                    except Exception as e:
                        print('出现问题>>>',e)
                else:
                    try:
                        srbase= ""
                    except Exception as e:
                        print("金额出现问题>>>",e)
            else:
                srbase=""
        except Exception as e:
            print('问题出在>>>',e)

        srbegin = re.findall('成立日期[\n\r\s]+(.*?)[\r\n\s]+',content)
        if len(srbegin)>0:
            srbegin=srbegin[0]
        else:
            srbegin=''

        srtype = re.findall('企业类型[\n\r\s]+(.*?)[\r\n\s]+',content)
        if len(srtype)>0:
            srtype=srtype[0]
        else:
            srtype=''

        srbussis = re.findall('经营范围[\n\r\s]+(.*?)[\r\n\s]+',content)
        if len(srbussis)>0:
            srbussis=srbussis[0]
        else:
            srbussis=''
        
        sradress = re.findall('(公司住所|注册地址)[\n\r\s]+(.*?)[\r\n\s]+',content)
        if len(sradress)>0:
            sradress=sradress[0][1]
        else:
            sradress=""

        srtime1 = re.findall('营业期限[\n\r\s]+(.*?)[\u4e00-\u9fa5]+',content)
        if len(srtime1)>0:
            srtime1 = srtime1[0]
        else:
            srtime1 = ""
        
        srtime2 = re.findall('核准日期[\n\r\s]+(.*?)[\r\n\s]+',content)
        if len(srtime2)>0:
            srtime2=srtime2[0]
        else:
            srtime2=""

        srorgan = re.findall('登记机关[\n\r\s]+(.*?)[\r\n\s]+',content)
        if len(srorgan)>0:
            srorgan = srorgan[0]
        else:
            srorgan='' 

        srstatus = re.findall('经营状态[\n\r\s]+(.*?)[\r\n\s]+',content)
        if len(srstatus)>0:
            try:
                srstatus=srstatus[0]
            except Exception as e:
                print("出现情况》》》",e)
                srstatus=""
        else:
            srstatus=""

        shareholder=re.findall('股东信息(.*?)主要人员',content)
        if len(shareholder)>0:
            try:
                shareholder=shareholder[0]
            except Exception as e:
                print("wenti >>",e)
                shareholder=''
        else:
            shareholder=''

        themain=re.findall('主要人员(.*?)变更记录',content)
        if len(themain)>0:
            try:
                themain=themain[0]
            except Exception as e:
                print("wenti >>",e)
                themain=''
        else:
            shareholder=''

        thenchange=re.findall('变更记录(.*?)[\n\r\s]+',content)
        if len(thenchange)>0:
            try:
                thenchange=thenchange[0]
            except Exception as e:
                print("wenti >>",e)
                thenchange=''
        else:
            thenchange=''

        # print('name="{}" value="{}"'.format(srname,srstatus))
        # print('name="{}" content="{}"'.format(srname,content))

        cursor.execute('update co_human_source set srboss="{}",srregistcard="{}",srlonelycard="{}",srbase="{}",srbegin="{}",srtype="{}",srbussis="{}",sradress="{}",srtime1="{}",srtime2="{}",srorgan="{}",srstatus="{}",shareholder="{}",themain="{}",thenchange="{}" where srname="{}"'.format(srboss,srregistcard,srloneycard,srbase,srbegin,srtype,srbussis,sradress,srtime1,srtime2,srorgan,srstatus,shareholder,themain,thenchange,srname))
        conn.commit()
        print("更改数据成功")
        self.close_mysql(conn,cursor)
    def close_mysql(self,conn,cursor):#关闭数据库
        try:
            cursor.close()
            sleep(1)
            conn.close()
            print("关闭数据库成功!")
        except Exception as e:
            print("关闭数据失败！>>>",e)
    
    def run(self):
        datas = self.output_mysql()
        for i in range(0,len(datas),1):
            self.input_mysql(datas[i])

if __name__=='__main__':
    humanClean().run()